package com.guigu.qingcheng.mapper;

import com.guigu.qingcheng.pojo.TbSku;
import com.guigu.qingcheng.pojo.dto.GoodsDto;
import org.apache.ibatis.annotations.Param;
import com.guigu.qingcheng.pojo.vo.SpuAndSku;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

public interface TbSkuMapper extends Mapper<TbSku> {
    /**
     * 查询所有未下架商品
     * @return
     */
    @Select("SELECT COUNT(*) sumsku3 FROM tb_sku WHERE STATUS=1 ")
    public Map findsku3();
    /**
     * 查询所有已下架商品
     * @return
     */
    @Select("SELECT COUNT(*) sumsku2 FROM tb_sku WHERE STATUS=2 ")
    public Map findsku2();
    /**
     * 查询所有商品
     * @return
     */
    @Select("SELECT COUNT(*) sumsku FROM tb_sku")
    public Map findsku();

    @Select("SELECT k.id,k.sn,k.name,k.`price`,k.`num`,k.`image`,k.`images`,k.`weight`,k.`create_time` createTime\n" +
            "    ,k.`update_time` updateTime,k.`spu_id` spuId,k.`alert_num` alertNum,k.`brand_name` brandName\n" +
            "    , k.`category_name` categoryName,k.`category_id` categoryId,k.`sale_num` saleNum\n" +
            "    ,k.`comment_num` commentNum,p.is_marketable pMarketable,p.`status` pStatus\n" +
            "    FROM tb_sku k LEFT JOIN tb_spu p ON k.spu_id = p.`id` where k.id = #{id}")
    SpuAndSku finkSkuAndSpuBySkuId(String id);


    /**
     * 根据商品id查询商品
     * @param id
     * @return
     */
    @Select("SELECT sk.spu_id spuid,sk.image,sp.name,sp.sn,sp.category1_id category1Id,sp.category2_id category2Id,sp.category3_id category3Id,sk.brand_name brandName,sk.price,sp.spec_items specItems,sk.num FROM tb_sku sk INNER JOIN tb_spu sp ON sk.spu_id=sp.id   WHERE sp.is_marketable='1' AND sp.is_delete='0' AND sk.id=#{id}")
    public GoodsDto queryByGoodsName(@Param("id") String id);


    /**
     *  根据商品名称查询商品
     * @param name
     * @return
     */
    @Select("SELECT id,NAME FROM tb_sku  WHERE STATUS='1' AND NAME LIKE CONCAT('%',#{name},'%') limit 20")
    public List<TbSku> queryByGoodsLikeName(@Param("name") String name);

    /**
     * 扣减库存
     * @param id
     * @param num
     */
    @Select("update tb_sku set num=num-#{num} where id=#{id}")
    public void deductionStock(@Param("id") String id, @Param("num") Integer num);

    /**
     * 添加销量
     * @param id
     * @param num
     */
    @Select("update tb_sku set sale_num=sale_num+#{num} where id=#{id}")
    public void addSaleNum(@Param("id") String id, @Param("num") Integer num);

    @Select("SELECT * FROM tb_sku WHERE spec LIKE CONCAT('%',#{spec},'%') AND spu_id=#{spuId}")
    public List<TbSku> querySkuGoodsBySpec(@Param("spec") String spec,@Param("spuId") String spuId);
}